clear
set obs 1
gen year =1979 
gen hhsize = 1
save temp, replace

global h1 = "ONE"
global h2 = "TWO"
global h3 = "THREE"
global h4 = "FOUR"
global h5 = "FIVE"
global h6 = "SIX"
global h7 = "SEVEN"
global h8 = "EIGHT"
global h9 = "NINE"


global address = "https://www2.census.gov/programs-surveys/cps/tables/time-series/historical-poverty-thresholds"

foreach yr of numlist 80/99 0/20  { //
	if length("`yr'")<2 local yr = "0`yr'"
	disp "`yr'"
	capture import delim "${address}/thresh`yr'.csv", delim(",") clear
	capture import excel "${address}/thresh`yr'.xls", cellrange(A9) allstring clear
	capture rename A v1
	capture rename B v2
	keep v1 v2 

	gen hhsize = .

	local i=1
	replace hhsize = `i' if strmatch(upper(v1[_n-1]),"*${h`i'} PERSON*")
	
	local i=2
	replace hhsize = `i' if strmatch(upper(v1[_n-1]),"*${h`i'} PERSONS*")
	replace hhsize = `i' if strmatch(upper(v1[_n-1]),"*${h`i'} PEOPLE*")

	forvalues i = 3/9 {
		replace hhsize = `i' if strmatch(upper(v1),"*${h`i'} PERSONS*")
		replace hhsize = `i' if strmatch(upper(v1),"*${h`i'} PEOPLE*")
	}

	drop if hhsize==.
	gen povline = subinstr(v2,",","",.)

	destring povline, replace
	rename v1 raw1
	rename v2 raw2
	format raw1 raw2 %6s
	gen year = 1900+`yr'
	replace year = 2000+`yr' if `yr'<40
	order year hhsize povline

	append using temp
	save temp, replace
}
*drop blank
format raw1 raw2 %6s
sort year hhsize
xtset hhsize year 
tsfill, full

*Fix missing years
replace povline = (68.3/77.8)*f.povline if year==1979 & povline==.
replace povline = (202.416/198.3)*l.povline if year==2007 & povline==.


tab year hhsize, m
save ${supp_data}/povline, replace
